﻿<cfsilent>
	<cfscript>
		
		sql = "  SELECT a.institute_id,a.institute_name 
					FROM t_institute a
					WHERE a.institute_id = :depId ";
    	queryObj = new Query( datasource=application.dnsSlave ) ;
    	queryObj.addParam( name="depId", value=event.getArg("DepID"), cfsqltype="cf_sql_varchar" );
   		rs_department = queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT a.sbj_id 
				FROM t_subject a 
				WHERE a.institute_id = :Institute 
					  AND
					  a.sbj_direction = :SubjectDirection  
				ORDER BY a.level_code ASC,a.term_start ASC,a.sbj_name ASC ";
		queryObj = new Query( datasource=application.dnsSlave ) ;
    	queryObj.addParam( name="Institute", value=rs_department.institute_id, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="SubjectDirection", value='0', cfsqltype="cf_sql_char" );
   		rs_subject = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_department.institute_name & "行政班信息", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("行政班", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "班级,计划人数,实际人数,年级,建班年月,班主任,班长,辅导员,所在校区,所属专业");
		
		row = 1;
		
		for (a=1; a LTE rs_subject.recordCount; a++ ) {
			
			sql = "SELECT  a.teacher_in_charge,
						   a.assistant,
						   a.cls_name,
						   a.cls_size,
						   a.cls_scale,
						   a.grade,
						   a.cls_found,
						   b.campus_name,
						   c.sbj_name
					FROM t_class a 
						 INNER JOIN t_campus b ON b.campus_id = a.campus_id 
						 INNER JOIN t_subject c ON c.sbj_id = a.sbj_id 
					WHERE a.sbj_id = :Subject 
					ORDER BY a.grade DESC ,a.cls_name ASC ";
			queryObj = new Query( datasource=application.dnsSlave ) ;
			queryObj.addParam( name="Subject", value=rs_subject["sbj_id"][a], cfsqltype="cf_sql_varchar" );
			rs_class = queryObj.execute( sql=sql ).getResult();
			
			for ( b=1; b LTE rs_class.recordCount; b++) {
				
				row++;
				
				TeacherInCharge = "";
				Monitor = "";
				Assistant = "";
				
				/* 查询班主任 */
				if ( rs_class["teacher_in_charge"][b] neq "" ) {
					
					sql = "SELECT a.tch_id, a.tch_name 
							FROM t_teacher a  
							WHERE a.tch_id = :tch_id ";
					queryObj = new Query( datasource=application.dnsSlave ) ;
					queryObj.addParam( name="tch_id", value=rs_class["teacher_in_charge"][b], cfsqltype="cf_sql_varchar" );
					rs_teacher = queryObj.execute( sql=sql ).getResult();
					
					if ( rs_teacher.recordCount ) {
						TeacherInCharge = rs_teacher["tch_name"][1] & "(" & rs_teacher["tch_id"][1] & ")";
					}
				}
				
				 /* 查询班长 */
				
				 /* 查询辅导员 */
				if ( rs_class["assistant"][b] neq "" ) {
					
					sql = "SELECT a.tch_id, a.tch_name 
							FROM t_teacher a  
							WHERE a.tch_id = :tch_id ";
					queryObj = new Query( datasource=application.dnsSlave ) ;
					queryObj.addParam( name="tch_id", value=rs_class["assistant"][b], cfsqltype="cf_sql_varchar" );
					rs_teacherAs = queryObj.execute( sql=sql ).getResult();
					
					if ( rs_teacherAs.recordCount ) {
						Assistant = rs_teacherAs["tch_name"][1] & "(" & rs_teacherAs["tch_id"][1] & ")";
					}
				}
				
				/* 构建班级信息 */
				rowData = rs_class["cls_name"][b]
						& ","
						& rs_class["cls_size"][b]
						& ","
						& rs_class["cls_scale"][b]
						& ","
						& rs_class["grade"][b]
						& ","
						& rs_class["cls_found"][b]
						& ","
						& TeacherInCharge
						& ","
						& Monitor
						& ","
						& Assistant
						& ","
						& rs_class["campus_name"][b]
						& ","
						& rs_class["sbj_name"][b];
				
				/* 写入班级信息 */
				excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
			}
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>